﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess;
using BussinessLogic.Models;
using BussinessLogic.Exercise;
using System.Data.Objects;
using BussinessLogic.Models.Administrator;
using OfficeOpenXml;
using OfficeOpenXml.Style;

namespace BussinessLogic.Services
{
    public static class SessionService
    {
        public static ActivityProtocol GetNextActivity(int studyId, string username, bool? initial)
        {
            using (var bdContext = new LicentaEntities())
            {
                bool isForApproval = initial != null ? (bool)initial : false;
                ActivityProtocol protocol;
                int userId = AccountService.GetUserID(username);
                var lastSession = bdContext.Sessions.Where(s => s.StudyID == studyId && s.UserID == userId && s.IsForApproval == isForApproval).OrderByDescending(s => s.Date).FirstOrDefault();
                StudyProtocolExercis protocolActivity;

                int activityId;
                int sessionId;
                int groupId;
                int remainingCrtActivity;
                int remainingCrtSession;
                int remainingCrtGroup;

                if (lastSession != null)
                {
                    var lastActivity = lastSession.SessionActivityResults.OrderByDescending(a => a.Date).FirstOrDefault();

                    activityId = lastActivity.ProtocolActivityID;
                    sessionId = lastActivity.ProtocolSessionID;
                    groupId = lastActivity.ProtocolGroupId;
                    remainingCrtActivity = lastActivity.RemainingActivity;
                    remainingCrtSession = lastActivity.RemainingSession;
                    remainingCrtGroup = lastActivity.RemainingGroup;


                    if (lastSession.Date.Date == DateTime.Today)
                    // Today session:
                    //  - repeat current exercise
                    //  - move to next exercise within session
                    //  - no exercises left in today session
                    {
                        if (remainingCrtActivity > 0)
                        {
                            // repeat current
                            protocolActivity = bdContext.StudyProtocolExercises.Where(ex => ex.ID == activityId).First();

                            remainingCrtActivity--;
                        }
                        else
                        {
                            // go to next exercise
                            bool next = false;
                            protocolActivity = null;
                            foreach (var exercise in bdContext.StudyProtocolExercises.Where(ex => ex.ID == activityId).First().StudyProtocolSession.StudyProtocolExercises.OrderBy(s => s.Sequence))
                            {
                                if (next)
                                {
                                    protocolActivity = exercise;

                                    activityId = exercise.ID;
                                    remainingCrtActivity = exercise.Repeat - 1;
                                    break;
                                }

                                if (exercise.ID == activityId)
                                {
                                    next = true;
                                }
                            }

                            if (protocolActivity != null)
                            {

                                //TODO return
                            }
                            else
                            {
                                // no more activities for today session
                                return null;
                            }
                        }
                    }
                    else
                    {
                        // New day session
                        // - repeat current session
                        // - move to next session - first activity
                        // - repeat crt group - first session - first activity
                        // - move to next group - first session - first activity
                        // - no groups left - study done

                        if (remainingCrtSession > 0)
                        {
                            //repeat current session    
                            protocolActivity = bdContext.StudyProtocolSessions.Where(s => s.ID == sessionId).First().StudyProtocolExercises.OrderBy(e => e.Sequence).First();

                            activityId = protocolActivity.ID;
                            remainingCrtActivity = protocolActivity.Repeat - 1;

                            remainingCrtSession = remainingCrtSession - 1;


                            //TODO return
                        }
                        else
                        {
                            // go to next session
                            bool next = false;
                            protocolActivity = null;
                            foreach (var session in bdContext.StudyProtocolSessions.Where(s => s.ID == sessionId).First().StudyProtocolGroup.StudyProtocolSessions.OrderBy(s => s.Sequence))
                            {
                                if (next)
                                {
                                    // take first activity from the session
                                    protocolActivity = bdContext.StudyProtocolSessions.Where(s => s.ID == session.ID).First().StudyProtocolExercises.OrderBy(e => e.Sequence).First();

                                    sessionId = protocolActivity.SessionID;
                                    remainingCrtSession = protocolActivity.StudyProtocolSession.Repeat - 1;

                                    activityId = protocolActivity.ID;
                                    remainingCrtActivity = protocolActivity.Repeat - 1;
                                    break;
                                }

                                if (session.ID == sessionId)
                                {
                                    next = true;
                                }
                            }

                            if (protocolActivity != null)
                            {
                                //TODO return
                            }
                            else
                            {
                                // no more session in crt group
                                if (remainingCrtGroup > 0)
                                {
                                    //repeat group
                                    protocolActivity = bdContext.StudyProtocolGroups.Where(g => g.ID == groupId).First().StudyProtocolSessions.OrderBy(s => s.Sequence).First().StudyProtocolExercises.OrderBy(e => e.Sequence).First();
                                    remainingCrtGroup--;

                                    sessionId = protocolActivity.SessionID;
                                    remainingCrtSession = protocolActivity.StudyProtocolSession.Repeat;

                                    activityId = protocolActivity.ID;
                                    remainingCrtActivity = protocolActivity.Repeat - 1;

                                }
                                else
                                {
                                    // move to next group

                                    next = false;
                                    protocolActivity = null;
                                    foreach (var group in bdContext.StudyProtocolGroups.Where(g => g.ID == groupId).First().StudyProtocol.StudyProtocolGroups.OrderBy(g => g.Sequence))
                                    {
                                        if (next)
                                        {
                                            // take first activity from the next group, first session
                                            protocolActivity = bdContext.StudyProtocolGroups.Where(g => g.ID == group.ID).First().StudyProtocolSessions.OrderBy(s => s.Sequence).First().StudyProtocolExercises.OrderBy(e => e.SessionID).First();

                                            groupId = group.ID;
                                            remainingCrtGroup = group.Repeat;

                                            sessionId = protocolActivity.SessionID;
                                            remainingCrtSession = protocolActivity.StudyProtocolSession.Repeat;

                                            activityId = protocolActivity.ID;
                                            remainingCrtActivity = protocolActivity.Repeat - 1;
                                            break;
                                        }

                                        if (group.ID == groupId)
                                        {
                                            next = true;
                                        }
                                    }

                                    if (next)
                                    {
                                        //TODO return
                                    }
                                }
                            }
                        }
                    }
                }
                else
                {
                    // take first group - first session - first exercise
                    protocolActivity = bdContext.StudyProtocols.Where(p => p.StudyID == studyId).First()
                             .StudyProtocolGroups.Where(g => g.IsForApproval == isForApproval).OrderBy(g => g.Sequence).First()
                                 .StudyProtocolSessions.OrderBy(s => s.Sequence).First()
                                     .StudyProtocolExercises.OrderBy(a => a.Sequence).First();

                    groupId = protocolActivity.StudyProtocolSession.StudyProtocolGroup.ID;
                    remainingCrtGroup = protocolActivity.StudyProtocolSession.StudyProtocolGroup.Repeat - 1;

                    sessionId = protocolActivity.SessionID;
                    remainingCrtSession = protocolActivity.StudyProtocolSession.Repeat - 1;

                    activityId = protocolActivity.ID;
                    remainingCrtActivity = protocolActivity.Repeat - 1;
                }

                protocol = GetProtocolFromActivity(protocolActivity);
                protocol.StudyID = studyId;

                protocol.RemainingCrtSession = remainingCrtSession;
                protocol.RemainingCrtActivity = remainingCrtActivity;
                protocol.RemainingCrtGroup = remainingCrtGroup;

                protocol.ProtocolSessionId = sessionId;
                protocol.ProtocolGroupId = groupId;
                protocol.ProtocolActivityId = activityId;


                return protocol;
            }
        }

        public static ActivityProtocol GetProtocolFromActivity(StudyProtocolExercis protocolActivity)
        {
            ActivityProtocol protocol = new ActivityProtocol();
            if (protocolActivity.Activity.ActivityType == (int)ActivityType.Questionnaire)
            {
                protocol = new ActivityProtocolQN();
                protocol.ProtocolActivityId = protocolActivity.ID;
                protocol.IsForApproval = protocolActivity.StudyProtocolSession.StudyProtocolGroup.IsForApproval;
                var qn = protocol as ActivityProtocolQN;
                qn.ActivityID = protocolActivity.Activity.ActivityQN.ID;
                qn.Questions = new List<DailyQuestion>();
                foreach (var question in protocolActivity.Activity.ActivityQN.ActivityQNQuestions)
                {
                    var q = new DailyQuestion
                    {
                        ID = question.ID,
                        Text = question.Text,
                        HasMultipleChoice = question.MultipleChoise,
                        HasFreeAnswer = question.FreeTextAnswer,
                    };

                    q.Answers = new List<DailyAnswer>();

                    foreach (var answer in question.ActivityQNAnswers)
                    {
                        q.Answers.Add(new DailyAnswer
                        {
                            ID = answer.ID,
                            Text = answer.Text,
                        });
                    }

                    qn.Questions.Add(q);
                }
            }
            else if (protocolActivity.Activity.ActivityType == (int)ActivityType.Exercise)
            {
                protocol = new ActivityProtocolExercise();
                protocol.ProtocolActivityId = protocolActivity.ID;
                protocol.IsForApproval = protocolActivity.StudyProtocolSession.StudyProtocolGroup.IsForApproval;
                var exercise = protocol as ActivityProtocolExercise;
                exercise.ActivityID = protocolActivity.Activity.ActivityExercise.ID;

                exercise.AcomodationTrials = protocolActivity.Activity.ActivityExercise.AcomodationTrials;
                exercise.CrossNumberTime = protocolActivity.Activity.ActivityExercise.NumberTimeout;
                exercise.FinalSession = protocolActivity.Activity.ActivityExercise.FinalSession;
                exercise.PictureTime = protocolActivity.Activity.ActivityExercise.PictureTimeout;
                exercise.FixationTrial = protocolActivity.Activity.ActivityExercise.FixationTrials;

                exercise.Negatives = new List<string>();
                foreach (var pic in protocolActivity.Activity.ActivityExercise.ActivityExercisePictures.Where(p => p.Type == (int)PictureType.Negative))
                {
                    exercise.Negatives.Add(pic.PictureName);
                }

                exercise.Neutrals = new List<string>();
                foreach (var pic in protocolActivity.Activity.ActivityExercise.ActivityExercisePictures.Where(p => p.Type == (int)PictureType.Neutral))
                {
                    exercise.Neutrals.Add(pic.PictureName);
                }

                exercise.TrainingTrials = new List<ExerciseTrial>();
                foreach (var trial in protocolActivity.Activity.ActivityExercise.ActivityExerciseCustomTrials)
                {
                    exercise.TrainingTrials.Add(new ExerciseTrial
                    {
                        Letter = (ExerciseTrial.LetterType)trial.Letter,
                        LetterPosition = (ExerciseTrial.Position)trial.LetterPosition,
                        NeutralPosition = (ExerciseTrial.Position)trial.NeutralPosition,
                        SType = ExerciseTrial.SequenceType.Training,
                        Repeat = trial.Repeat
                    });
                }


            }
            else if (protocolActivity.Activity.ActivityType == (int)ActivityType.MatrixExercise)
            {
                protocol = new ActivityProtocolMatrix();
                protocol.ProtocolActivityId = protocolActivity.ID;
                protocol.IsForApproval = protocolActivity.StudyProtocolSession.StudyProtocolGroup.IsForApproval;
                var exercise = protocol as ActivityProtocolMatrix;
                exercise.TrialCount = protocolActivity.Activity.ActivityMatrixExercis.TrialCount;
                exercise.ActivityID = protocolActivity.Activity.ActivityMatrixExercis.ID;

                exercise.Negatives = new List<string>();
                foreach (var pic in protocolActivity.Activity.ActivityMatrixExercis.ActivityMatrixExercisePictures.Where(p => p.Type == (int)PictureType.Negative))
                {
                    exercise.Negatives.Add(pic.PictureName);
                }

                exercise.Neutrals = new List<string>();
                foreach (var pic in protocolActivity.Activity.ActivityMatrixExercis.ActivityMatrixExercisePictures.Where(p => p.Type == (int)PictureType.Neutral))
                {
                    exercise.Neutrals.Add(pic.PictureName);
                }
            }


            return protocol;
        }

        public static void SaveSessionQn(QuestionnaireResult result)
        {
            using (var bdContext = new LicentaEntities())
            {
                ComputeQnScore(result);

                int userId = AccountService.GetUserID(result.UserName);

                var session = bdContext.Sessions.Where(s => s.StudyID == result.StudyID && s.UserID == userId && EntityFunctions.TruncateTime(s.Date) == EntityFunctions.TruncateTime(DateTime.Today) && !s.IsForApproval).FirstOrDefault();

                if (session == null)
                {
                    session = new Session
                    {
                        IsForApproval = result.IsForApproval,
                        Date = DateTime.Now.Date,
                        UserID = userId,
                        StudyID = result.StudyID,
                    };

                    bdContext.Sessions.Add(session);
                    //bdContext.SaveChanges();
                }

                session.IsForApproval = result.IsForApproval;

                var activityResult = new SessionActivityResult
                {
                    ProtocolGroupId = result.ProtocolGroupId,
                    ProtocolSessionID = result.ProtocolSessionId,
                    ProtocolActivityID = result.ProtocolActivityId,

                    RemainingActivity = result.RemainingCrtActivity,
                    RemainingSession = result.RemainingCrtSession,
                    RemainingGroup = result.RemainingCrtGroup,

                    Date = DateTime.Now,
                };

                var qnResult = new SessionResultQN
                {
                    Score = result.Score,
                    QNID = result.ActivityID,
                };

                activityResult.ActivityType = (int)ActivityType.Questionnaire;

                foreach (var question in result.Questions)
                {
                    var questionResult = new SessionResultQNQuestion
                    {
                        QuestionID = question.ID
                    };

                    if (question.HasFreeAnswer)
                    {
                        questionResult.SessionResultQNAnswers.Add(new SessionResultQNAnswer
                        {
                            Text = question.FreeAnswer,
                            QuestionId = question.ID
                        });
                    }
                    else if (!question.HasMultipleChoice)
                    {
                        questionResult.SessionResultQNAnswers.Add(new SessionResultQNAnswer
                        {
                            AnswerID = question.SingleAnswerID,
                            QuestionId = question.ID
                        });
                    }
                    else
                    {
                        foreach (var answerId in question.Answers)
                        {
                            questionResult.SessionResultQNAnswers.Add(new SessionResultQNAnswer
                            {
                                AnswerID = answerId,
                                QuestionId = question.ID
                            });
                        }
                    }

                    qnResult.SessionResultQNQuestions.Add(questionResult);
                }
                activityResult.Session = session;
                activityResult.ActivityID = result.ActivityID;
                activityResult.SessionResultQN = qnResult;
                session.SessionActivityResults.Add(activityResult);

                bdContext.SaveChanges();
            }
        }

        public static void ComputeQnScore(QuestionnaireResult result)
        {
            using (var bdContext = new LicentaEntities())
            {
                foreach (var q in result.Questions)
                {
                    q.Score = 0;
                    if (q.HasFreeAnswer)
                    {
                        q.Score = 0;
                    }
                    else if (!q.HasMultipleChoice)
                    {
                        q.Score = bdContext.ActivityQNs.Where(qn => qn.ID == result.ActivityID).First().ActivityQNQuestions.Where(qu => qu.ID == q.ID).First().ActivityQNAnswers.Where(aq => aq.ID == q.SingleAnswerID).First().Value;
                    }
                    else
                    {
                        foreach (var answerId in q.Answers)
                        {
                            q.Score += bdContext.ActivityQNs.Where(qn => qn.ID == result.ActivityID).First().ActivityQNQuestions.Where(qu => qu.ID == q.ID).First().ActivityQNAnswers.Where(aq => aq.ID == answerId).First().Value;
                        }
                    }

                    result.Score += q.Score;
                }
            }
        }

        public static void SaveSessionExercise(List<ExerciseTrial> trials, string username, int studyId, ExerciseInfoResult einfo)
        {
            using (var bdContext = new LicentaEntities())
            {
                int userId = AccountService.GetUserID(username);

                var session = bdContext.Sessions.Where(s => s.StudyID == studyId && s.UserID == userId && EntityFunctions.TruncateTime(s.Date) == EntityFunctions.TruncateTime(DateTime.Today) && !s.IsForApproval).FirstOrDefault();

                if (session == null)
                {
                    session = new Session
                    {
                        IsForApproval = false,
                        Date = DateTime.Now.Date,
                        UserID = userId,
                        StudyID = studyId,
                    };

                    bdContext.Sessions.Add(session);
                }

                var activityResult = new SessionActivityResult
                {
                    ProtocolGroupId = einfo.ProtocolGroupId,
                    ProtocolSessionID = einfo.ProtocolSessionId,
                    ProtocolActivityID = einfo.ProtocolActivityId,

                    RemainingActivity = einfo.RemainingCrtActivity,
                    RemainingSession = einfo.RemainingCrtSession,
                    RemainingGroup = einfo.RemainingCrtGroup,

                    Date = DateTime.Now,
                };

                var exResult = new SessionResultExercis
                {
                    ExerciseId = einfo.ActivityID,
                };

                foreach (var t in trials)
                {
                    exResult.SessionResultTrials.Add(new SessionResultTrial
                    {
                        Letter = (int)t.Letter,
                        LetterPosition = (int)t.LetterPosition,
                        NeutralPosition = (int)t.NeutralPosition,
                        Reaction = t.ReactionTime,
                        Correct = t.Correct,
                        NegativePicture = t.NegativePhoto,
                        NeutralPicture = t.NeutralPhoto,
                        TrialType = (int)t.SType
                    });
                }

                session.SessionActivityResults.Add(activityResult);
                activityResult.ActivityID = einfo.ActivityID;
                activityResult.SessionResultExercis = exResult;

                bdContext.SaveChanges();

            }
        }

        public static List<SessionExercise> GetUserSessionDetails(int? id, int? sid, int? uid)
        {
            using (var bdContext = new LicentaEntities())
            {
                var result = new List<SessionExercise>();
                List<SessionResultQN> sessionsQN;
                List<SessionResultExercis> sessionsEx;
                List<SessionResultMatrixExcercis> sessionsExMatrix;
                if (id != null)
                {
                    sessionsQN = bdContext.Sessions.Where(s => s.ID == id).First().SessionActivityResults.Where(sr => sr.ActivityType == (int)ActivityType.Questionnaire).OrderBy(q => q.Date).Select(s => s.SessionResultQN).ToList();
                    sessionsEx = bdContext.Sessions.Where(s => s.ID == id).First().SessionActivityResults.Where(sr => sr.ActivityType == (int)ActivityType.Exercise).OrderBy(q => q.Date).Select(s => s.SessionResultExercis).ToList();
                    sessionsExMatrix = bdContext.Sessions.Where(s => s.ID == id).First().SessionActivityResults.Where(sr => sr.ActivityType == (int)ActivityType.MatrixExercise).OrderBy(q => q.Date).Select(s => s.SessionResultMatrixExcercis).ToList();
                }
                else
                { // Initial test only
                    sessionsQN = bdContext.Sessions.Where(s => s.StudyID == (int)sid && s.UserID == (int)uid).First().SessionActivityResults.Where(sr => sr.ActivityType == (int)ActivityType.Questionnaire).OrderBy(q => q.Date).Select(s => s.SessionResultQN).ToList();
                    sessionsEx = bdContext.Sessions.Where(s => s.StudyID == (int)sid && s.UserID == (int)uid).First().SessionActivityResults.Where(sr => sr.ActivityType == (int)ActivityType.Exercise).OrderBy(q => q.Date).Select(s => s.SessionResultExercis).ToList();
                    sessionsExMatrix = bdContext.Sessions.Where(s => s.StudyID == (int)sid && s.UserID == (int)uid).First().SessionActivityResults.Where(sr => sr.ActivityType == (int)ActivityType.MatrixExercise).OrderBy(q => q.Date).Select(s => s.SessionResultMatrixExcercis).ToList();
                }

                foreach (var qn in sessionsQN)
                {
                    var ex = new SessionExercise
                    {
                        ExType = Models.Administrator.ExerciseType.Questionnaire,
                        Name = qn.ActivityQN.Activity.Name,
                        QNTotalScore = qn.Score,
                    };

                    ex.Questionnaire = new QuestionnaireExercise();
                    ex.Questionnaire.Questions = new List<ExQnQuestion>();
                    foreach (var question in qn.SessionResultQNQuestions)
                    {
                        var q = new ExQnQuestion
                        {
                            Text = question.ActivityQNQuestion.Text
                        };

                        q.IsFreeAnswer = question.ActivityQNQuestion.FreeTextAnswer;
                        if (q.IsFreeAnswer)
                        {
                            q.FreeAnswer = question.SessionResultQNAnswers.First().Text;
                            q.Score = question.SessionResultQNAnswers.First().FreeAnswerScore;
                            q.FreeAnswerID = question.SessionResultQNAnswers.First().ID;
                        }
                        else
                        {
                            q.Score = 0;
                            foreach (var answer in question.SessionResultQNAnswers)
                            {
                                q.Score += answer.ActivityQNAnswer.Value;
                            }
                        }
                        ex.Questionnaire.Questions.Add(q);
                    }
                    result.Add(ex);
                }

                foreach (var ex in sessionsExMatrix)
                {
                    var exSession = new SessionExercise
                    {
                        ExType = Models.Administrator.ExerciseType.ExerciseMatrix,
                        Name = ex.ActivityMatrixExercis.Activity.Name,
                    };

                    exSession.ExerciseMatrixTrials = new List<SessionMatrixTrial>();
                    foreach (var trial in ex.SessionResultMatrixTrials.OrderBy(t => t.ID))
                    {
                        exSession.ExerciseMatrixTrials.Add(new SessionMatrixTrial
                        {
                            Picture = trial.NeutralPhoto,
                            Reaction = trial.Reaction
                        });
                    }

                    result.Add(exSession);
                }

                foreach (var ex in sessionsEx)
                {
                    var exSession = new SessionExercise
                    {
                        ExType = Models.Administrator.ExerciseType.Exercise,
                        Name = ex.ActivityExercise.Activity.Name,
                    };

                    exSession.ExerciseTrials = new List<SessionTrial>();
                    foreach (var trial in ex.SessionResultTrials.OrderBy(t => t.ID))
                    {
                        exSession.ExerciseTrials.Add(new SessionTrial
                        {
                            Correct = trial.Correct,
                            Digit = trial.Digit,
                            TrialType = (BussinessLogic.Exercise.ExerciseTrial.SequenceType)trial.TrialType,
                            Letter = (ExerciseTrial.LetterType)trial.Letter,
                            LetterPosition = (ExerciseTrial.Position)trial.LetterPosition,
                            NeutralPosition = (ExerciseTrial.Position)trial.NeutralPosition,
                            NeutralPicture = trial.NeutralPicture,
                            NegativePicture = trial.NegativePicture,
                            Reaction = trial.Reaction,
                        });
                    }

                    result.Add(exSession);
                }
                return result;

            }
        }

        public static void SaveScore(List<SaveScore> res)
        {
            using (var bdContext = new LicentaEntities())
            {
                foreach (var answerResult in res)
                {
                    var ans = bdContext.SessionResultQNAnswers.Where(a => a.ID == answerResult.ID).First();
                    ans.FreeAnswerScore = answerResult.Value;

                    int score = 0;
                    foreach (var question in ans.SessionResultQNQuestion.SessionResultQN.SessionResultQNQuestions)
                    {
                        foreach (var answer in question.SessionResultQNAnswers)
                        {
                            if (answer.AnswerID == null)
                            {
                                score += answer.FreeAnswerScore;
                            }
                            else
                            {
                                score += answer.ActivityQNAnswer.Value;
                            }
                        }
                    }
                    ans.SessionResultQNQuestion.SessionResultQN.Score = score;

                    bdContext.SaveChanges();
                }

            }
        }

        public static byte[] GetReportsForExercise(int id, out string fileName)
        {
            using (ExcelPackage pck = new ExcelPackage())
            using (var bdContext = new LicentaEntities())
            {
                fileName = string.Empty;
                var sessions = bdContext.Sessions.Where(s => s.StudyID == id);

                if (sessions.FirstOrDefault() == null)
                {
                    return null;
                }

                fileName = sessions.First().Study.Name + "_exercise" + ".xlsx";

                ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sessions.First().Study.Name);

                int row = 1;
                int col = 1;

                ws.Cells[row, col++].LoadFromText("Date");
                ws.Cells[row, col++].LoadFromText("User");
                ws.Cells[row, col++].LoadFromText("Exercise Name");

                ws.Cells[row, col++].LoadFromText("Convergent");
                ws.Cells[row, col++].LoadFromText("Trial Type");

                ws.Cells[row, col++].LoadFromText("Letter");
                ws.Cells[row, col++].LoadFromText("Letter Position");
                ws.Cells[row, col++].LoadFromText("NeutralPosition");
                ws.Cells[row, col++].LoadFromText("Reaction");
                ws.Cells[row, col++].LoadFromText("Correct");
                ws.Cells[row, col++].LoadFromText("NeutralPicture");
                ws.Cells[row, col++].LoadFromText("NegativePicture");

                col = 1;
                row++;

                foreach (var session in sessions)
                {
                    foreach (var exercise in session.SessionActivityResults.Where(ar => ar.ActivityType == (int)ActivityType.Exercise).OrderBy(a => a.Date).Select(ar => ar.SessionResultExercis))
                    {
                        foreach (var trial in exercise.SessionResultTrials)
                        {
                            ws.Cells[row, col].Style.Numberformat.Format = "m/d/yyyy";
                            ws.Cells[row, col++].LoadFromText(exercise.SessionActivityResult.Date.Date.ToString());

                            ws.Cells[row, col++].LoadFromText(session.User.Email);
                            ws.Cells[row, col++].LoadFromText(exercise.ActivityExercise.Activity.Name);


                            ws.Cells[row, col++].LoadFromText(trial.LetterPosition != trial.NeutralPosition ? "Yes" : "No");
                            ws.Cells[row, col++].LoadFromText(((ExerciseTrial.SequenceType)trial.TrialType).ToString());
                            ws.Cells[row, col++].LoadFromText(((ExerciseTrial.LetterType)trial.Letter).ToString());
                            ws.Cells[row, col++].LoadFromText(((ExerciseTrial.Position)trial.LetterPosition).ToString());
                            ws.Cells[row, col++].LoadFromText(((ExerciseTrial.LetterType)trial.NeutralPosition).ToString());
                            ws.Cells[row, col++].LoadFromText((trial.Reaction).ToString());
                            ws.Cells[row, col++].LoadFromText(trial.Correct ? "Yes" : "No");
                            if (trial.NeutralPicture != null)
                            {
                                ws.Cells[row, col].LoadFromText(trial.NeutralPicture);
                            }
                            col++;
                            if (trial.NegativePicture != null)
                            {
                                ws.Cells[row, col].LoadFromText(trial.NegativePicture);
                            }
                            col++;


                            col = 1;
                            row++;
                        }

                    }
                }

                return pck.GetAsByteArray();
            }
        }

        public static byte[] GetReportsForQN(int id, out string fileName)
        {
            using (ExcelPackage pck = new ExcelPackage())
            using (var bdContext = new LicentaEntities())
            {
                fileName = string.Empty;
                var sessions = bdContext.Sessions.Where(s => s.StudyID == id);

                if (sessions.FirstOrDefault() == null)
                {
                    return null;
                }

                fileName = sessions.First().Study.Name + "_questionnaire.xlsx";

                ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sessions.First().Study.Name);

                int row = 1;
                int col = 1;

                ws.Cells[row, col++].LoadFromText("Date");
                ws.Cells[row, col++].LoadFromText("User");

                foreach (var session in sessions)
                {
                    foreach (var qn in session.SessionActivityResults.Where(ar => ar.ActivityType == (int)ActivityType.Questionnaire).OrderBy(a => a.Date).Select(a => a.SessionResultQN))
                    {
                        ws.Cells[row, col++].LoadFromText(qn.ActivityQN.Activity.Name + ": Score");

                        foreach (var q in qn.SessionResultQNQuestions)
                        {
                            ws.Cells[row, col++].LoadFromText(q.ActivityQNQuestion.Text);

                        }
                    }
                }

                row++;
                col = 1;

                foreach (var session in sessions)
                {
                    ws.Cells[row, col].Style.Numberformat.Format = "m/d/yyyy";
                    ws.Cells[row, col++].LoadFromText(session.Date.ToString());
                    ws.Cells[row, col++].LoadFromText(session.User.Email);

                    foreach (var qn in session.SessionActivityResults.Where(ar => ar.ActivityType == (int)ActivityType.Questionnaire).OrderBy(a => a.Date).Select(a => a.SessionResultQN))
                    {

                        ws.Cells[row, col++].LoadFromText(qn.Score.ToString());


                        foreach (var q in qn.SessionResultQNQuestions)
                        {
                            if (!q.ActivityQNQuestion.FreeTextAnswer)
                            {
                                if (!q.ActivityQNQuestion.MultipleChoise)
                                {
                                    ws.Cells[row, col++].LoadFromText(q.SessionResultQNAnswers.First().ActivityQNAnswer.Value.ToString());
                                }
                                else
                                {
                                    int score = 0;
                                    foreach (var item in q.SessionResultQNAnswers)
                                    {
                                        score += item.ActivityQNAnswer.Value;
                                    }
                                    ws.Cells[row, col++].LoadFromText(score.ToString());
                                }
                            }
                            else
                            {
                                ws.Cells[row, col++].LoadFromText(q.SessionResultQNAnswers.First().FreeAnswerScore.ToString());
                            }

                        }
                    }
                    row++;
                    col = 1;
                }
                return pck.GetAsByteArray();
            }
        }

        public static void SaveSessionMatrixExercise(List<MatrixTrial> trials, string username, int StudyID, ExerciseInfoResult eInfo)
        {
            using (var bdContext = new LicentaEntities())
            {
                int userId = AccountService.GetUserID(username);

                var session = bdContext.Sessions.Where(s => s.StudyID == StudyID && s.UserID == userId && EntityFunctions.TruncateTime(s.Date) == EntityFunctions.TruncateTime(DateTime.Today) && !s.IsForApproval).FirstOrDefault();

                if (session == null)
                {
                    session = new Session
                    {
                        IsForApproval = false,
                        Date = DateTime.Now.Date,
                        UserID = userId,
                        StudyID = StudyID,
                    };

                    bdContext.Sessions.Add(session);
                }

                var activityResult = new SessionActivityResult
                {
                    ProtocolGroupId = eInfo.ProtocolGroupId,
                    ProtocolSessionID = eInfo.ProtocolSessionId,
                    ProtocolActivityID = eInfo.ProtocolActivityId,

                    RemainingActivity = eInfo.RemainingCrtActivity,
                    RemainingSession = eInfo.RemainingCrtSession,
                    RemainingGroup = eInfo.RemainingCrtGroup,

                    Date = DateTime.Now,
                    ActivityType = (int)ActivityType.MatrixExercise
                };

                var exResult = new SessionResultMatrixExcercis
                {
                    MatrixExerciseID = eInfo.ActivityID,
                };

                foreach (var t in trials)
                {
                    exResult.SessionResultMatrixTrials.Add(new SessionResultMatrixTrial
                    {
                        NeutralPhoto = t.NeutralPhoto,
                        Reaction = t.Reaction
                    });
                }

                activityResult.ActivityID = eInfo.ActivityID;
                session.SessionActivityResults.Add(activityResult);

                activityResult.SessionResultMatrixExcercis = exResult;

                bdContext.SaveChanges();

            }
        }
    }
}
